﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class InvTransDA : IInvTransDA
    {
        private string CASE_CNT = "@CASE_CNT";
        private string CREATED_BY = "@CREATED_BY";
        private string CUBE = "@CUBE";
        private string DOC_ID = "@DOC_ID";
        private string DOC_LINE_ID = "@DOC_LINE_ID";
        private string EXPIRY_DATE = "@EXPIRY_DATE";
        private string EXTERNAL_LOT = "@EXTERNAL_LOT";
        private string FROM_LOC = "@FROM_LOC";
        private string FROM_LPN_ID = "@FROM_LPN_ID";
        private string GROSS_WGT = "@GROSS_WGT";
        private string HOLD_CODE = "@HOLD_CODE";
        private string INNER_PACK = "@INNER_PACK";
        private string LOT = "@LOT";
        private string LOT_ATTR01 = "@LOT_ATTR01";
        private string LOT_ATTR02 = "@LOT_ATTR02";
        private string LOT_ATTR03 = "@LOT_ATTR03";
        private string LOT_ATTR04 = "@LOT_ATTR04";
        private string LOT_ATTR05 = "@LOT_ATTR05";
        private string LOT_ATTR06 = "@LOT_ATTR06";
        private string LOT_ATTR07 = "@LOT_ATTR07";
        private string LOT_ATTR08 = "@LOT_ATTR08";
        private string LOT_ATTR09 = "@LOT_ATTR09";
        private string LOT_ATTR10 = "@LOT_ATTR10";
        private string NET_WGT = "@NET_WGT";
        private string OTHER_UNIT1 = "@OTHER_UNIT1";
        private string OTHER_UNIT2 = "@OTHER_UNIT2";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string PALLET = "@PALLET";
        private string PRODUCE_DATE = "@PRODUCE_DATE";
        private string QTY = "@QTY";
        private string RECEIPT_DATE = "@RECEIPT_DATE";
        private string RECEIPT_ID = "@RECEIPT_ID";
        private string RECEIPT_LINE_ID = "@RECEIPT_LINE_ID";
        private string SKU_ID = "@SKU_ID";
        private string SOURCE_TRANS_TYPE = "@SOURCE_TRANS_TYPE";
        private string SQL_CHECK_INV_TRANS_ID_UNIQUE = " SELECT COUNT(1) FROM @_@INV_TRANS WHERE TRANS_ID = @TRANS_ID ";
        private string SQL_DELETE_INV_TRANS = " DELETE FROM @_@INV_TRANS WHERE TRANS_ID = @TRANS_ID ";
        private string SQL_INSERT_INV_TRANS = " INSERT INTO @_@INV_TRANS ( WH_ID, TRANS_ID, TRANS_TYPE, OWNER_ID, SKU_ID, LOT, FROM_LOC, FROM_LPN_ID, TO_LOC, TO_LPN_ID, DOC_ID, DOC_LINE_ID, SOURCE_TRANS_TYPE, STATUS, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, EXPIRY_DATE, RECEIPT_DATE, EXTERNAL_LOT,VENDOR_ID, QTY, CASE_CNT, INNER_PACK, PALLET, CUBE, GROSS_WGT, NET_WGT, OTHER_UNIT1, OTHER_UNIT2, PACK_ID, UOM_ID, UOM_CALC, UOM_QTY, RECEIPT_ID, RECEIPT_LINE_ID, HOLD_CODE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,UNIT_PRICE,TRANS_DATE) VALUES ( @WH_ID, @TRANS_ID, @TRANS_TYPE, @OWNER_ID, @SKU_ID, @LOT, @FROM_LOC, @FROM_LPN_ID, @TO_LOC, @TO_LPN_ID, @DOC_ID, @DOC_LINE_ID, @SOURCE_TRANS_TYPE, @STATUS, @LOT_ATTR01, @LOT_ATTR02, @LOT_ATTR03, @LOT_ATTR04, @LOT_ATTR05, @LOT_ATTR06, @LOT_ATTR07, @LOT_ATTR08, @LOT_ATTR09, @LOT_ATTR10, @PRODUCE_DATE, @EXPIRY_DATE, @RECEIPT_DATE, @EXTERNAL_LOT, @VENDOR_ID, @QTY, @CASE_CNT, @INNER_PACK, @PALLET, @CUBE, @GROSS_WGT, @NET_WGT, @OTHER_UNIT1, @OTHER_UNIT2, @PACK_ID, @UOM_ID, @UOM_CALC, @UOM_QTY, @RECEIPT_ID, @RECEIPT_LINE_ID, @HOLD_CODE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(),@UNIT_PRICE,@TRANS_DATE )  ";
        private string SQL_SELECT_ALL_INV_TRANS = " SELECT IT.ROWID, IT.WH_ID, IT.TRANS_ID, IT.TRANS_TYPE, T.[TEXT] AS TRANS_TYPE_D,IT.OWNER_ID AS OWNER_ID, IT.SKU_ID AS SKU_ID, IT.LOT AS LOT, IT.FROM_LOC AS FROM_LOC, IT.FROM_LPN_ID AS FROM_LPN_ID, IT.TO_LOC AS TO_LOC, IT.TO_LPN_ID AS TO_LPN_ID, IT.DOC_ID AS DOC_ID, IT.DOC_LINE_ID AS DOC_LINE_ID, IT.SOURCE_TRANS_TYPE AS SOURCE_TRANS_TYPE, T2.[TEXT] AS SOURCE_TRANS_TYPE_D, IT.STATUS AS STATUS, T3.[TEXT] AS STATUS_D, IT.LOT_ATTR01, IT.LOT_ATTR02, IT.LOT_ATTR03, IT.LOT_ATTR04, IT.LOT_ATTR05, IT.LOT_ATTR06, IT.LOT_ATTR07, IT.LOT_ATTR08, IT.LOT_ATTR09, IT.LOT_ATTR10, IT.PRODUCE_DATE, IT.EXPIRY_DATE, IT.RECEIPT_DATE, IT.EXTERNAL_LOT, IT.QTY, IT.CASE_CNT, IT.INNER_PACK, IT.PALLET, IT.CUBE, IT.GROSS_WGT, IT.NET_WGT, IT.OTHER_UNIT1, IT.OTHER_UNIT2, IT.PACK_ID AS PACK_ID, IT.UOM_ID AS UOM_ID, IT.UOM_CALC, IT.UOM_QTY, IT.RECEIPT_ID AS RECEIPT_ID, IT.RECEIPT_LINE_ID AS RECEIPT_LINE_ID, IT.HOLD_CODE, IT.TS, IT.CREATED_BY AS CREATED_BY, IT.CREATED_DATE AS CREATED_DATE, IT.UPDATED_BY AS UPDATED_BY, IT.UPDATED_DATE AS UPDATED_DATE ,VENDOR_ID AS VENDOR_ID,SKU.DESCR AS DESCR,IT.UNIT_PRICE AS UNIT_PRICE,IT.TRANS_DATE AS TRANS_DATE  FROM  @_@INV_TRANS IT LEFT JOIN @_@SKU SKU ON IT.OWNER_ID = SKU.OWNER_ID AND IT.SKU_ID = SKU.SKU_ID   LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = IT.TRANS_TYPE AND SCD.SYS_CODE_TYPE = 'TransType' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = IT.SOURCE_TRANS_TYPE AND SCD2.SYS_CODE_TYPE = 'TransSourceType' LEFT JOIN @_@SYS_CODE_DETAIL SCD3 ON SCD3.SYS_CODE = IT.STATUS AND SCD2.SYS_CODE_TYPE = 'TransStatus' LEFT JOIN sysadm.TRANSLATION T ON 'TransType.'+IT.TRANS_TYPE = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'TransSourceType.'+IT.SOURCE_TRANS_TYPE = T2.ITEM_ID  \t\t\tAND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T3 ON 'TransStatus.'+IT.STATUS = T3.ITEM_ID  \t\t\tAND  T3.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T3.LANG+'-'+T3.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON IT.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_INV_TRANS = " SELECT ROWID, WH_ID,TRANS_ID, TRANS_TYPE,OWNER_ID,SKU_ID, LOT, FROM_LOC, FROM_LPN_ID, TO_LOC, TO_LPN_ID, DOC_ID, DOC_LINE_ID, SOURCE_TRANS_TYPE, STATUS,LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, EXPIRY_DATE, RECEIPT_DATE, EXTERNAL_LOT, QTY, CASE_CNT,INNER_PACK, PALLET, CUBE, GROSS_WGT, NET_WGT, OTHER_UNIT1, OTHER_UNIT2, PACK_ID,UOM_ID, UOM_CALC, UOM_QTY, RECEIPT_ID,RECEIPT_LINE_ID, HOLD_CODE, TS, CREATED_BY, CREATED_DATE, UPDATED_BY,UPDATED_DATE ,VENDOR_ID,UNIT_PRICE,TRANS_DATE  FROM  @_@INV_TRANS WHERE CREATED_DATE BETWEEN '@CREATED_DATE 00:00:00' AND '@CREATED_DATE 23:59:59' ORDER BY CREATED_DATE";
        private string SQL_UPDATE_CREATED_DATE_BY_DOC_ID = " UPDATE @_@INV_TRANS SET TRANS_DATE=@TRANS_DATE ,UPDATED_DATE=@UPDATED_DATE, UPDATED_BY=@UPDATED_BY WHERE DOC_ID=@DOC_ID AND TRANS_TYPE=@TRANS_TYPE AND SOURCE_TRANS_TYPE=@SOURCE_TRANS_TYPE ";
        private string SQL_UPDATE_INV_TRANS = " UPDATE @_@INV_TRANS SET WH_ID = @WH_ID, TRANS_TYPE = @TRANS_TYPE, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, LOT = @LOT, FROM_LOC = @FROM_LOC, FROM_LPN_ID = @FROM_LPN_ID, TO_LOC = @TO_LOC, TO_LPN_ID = @TO_LPN_ID, DOC_ID = @DOC_ID, DOC_LINE_ID = @DOC_LINE_ID, SOURCE_TRANS_TYPE = @SOURCE_TRANS_TYPE, STATUS = @STATUS, LOT_ATTR01 = @LOT_ATTR01, LOT_ATTR02 = @LOT_ATTR02, LOT_ATTR03 = @LOT_ATTR03, LOT_ATTR04 = @LOT_ATTR04, LOT_ATTR05 = @LOT_ATTR05, LOT_ATTR06 = @LOT_ATTR06, LOT_ATTR07 = @LOT_ATTR07, LOT_ATTR08 = @LOT_ATTR08, LOT_ATTR09 = @LOT_ATTR09, LOT_ATTR10 = @LOT_ATTR10, PRODUCE_DATE = @PRODUCE_DATE, EXPIRY_DATE = @EXPIRY_DATE, RECEIPT_DATE = @RECEIPT_DATE, EXTERNAL_LOT = @EXTERNAL_LOT, VENDOR_ID = @VENDOR_ID,QTY = @QTY, CASE_CNT = @CASE_CNT, INNER_PACK = @INNER_PACK, PALLET = @PALLET, CUBE = @CUBE, GROSS_WGT = @GROSS_WGT, NET_WGT = @NET_WGT, OTHER_UNIT1 = @OTHER_UNIT1, OTHER_UNIT2 = @OTHER_UNIT2, PACK_ID = @PACK_ID, UOM_ID = @UOM_ID, UOM_CALC = @UOM_CALC, UOM_QTY = @UOM_QTY, RECEIPT_ID = @RECEIPT_ID, RECEIPT_LINE_ID = @RECEIPT_LINE_ID, HOLD_CODE = @HOLD_CODE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),UNIT_PRICE=@UNIT_PRICE,TRANS_DATE=@TRANS_DATE WHERE TRANS_ID = @TRANS_ID  ";
        private string SQL_UPDATE_INV_TRANS_UNIT_PRICE_BY_LOT = " UPDATE  @_@INV_TRANS  SET UNIT_PRICE=@UNIT_PRICE , UPDATED_BY=@UPDATED_BY,UPDATED_DATE=GETDATE()  WHERE LOT=@LOT";
        private string SQL_UPDATE_STATUS_BY_DOC_ID = " UPDATE @_@INV_TRANS SET STATUS = @STATUS,UPDATED_DATE=GETDATE(), UPDATED_BY=@UPDATED_BY WHERE DOC_ID = @DOC_ID AND TRANS_TYPE=@TRANS_TYPE AND SOURCE_TRANS_TYPE=@SOURCE_TRANS_TYPE ";
        private string SQL_UPDATE_STATUS_FOR_RECEIPT = " UPDATE @_@INV_TRANS SET STATUS = @STATUS,UPDATED_DATE=GETDATE(), UPDATED_BY=@UPDATED_BY WHERE TRANS_ID = @TRANS_ID";
        private string STATUS = "@STATUS";
        private string TO_LOC = "@TO_LOC";
        private string TO_LPN_ID = "@TO_LPN_ID";
        private string TRANS_DATE = "@TRANS_DATE";
        private string TRANS_ID = "@TRANS_ID";
        private string TRANS_TYPE = "@TRANS_TYPE";
        private string TS = "@TS";
        private string UNIT_PRICE = "@UNIT_PRICE";
        private string UOM_CALC = "@UOM_CALC";
        private string UOM_ID = "@UOM_ID";
        private string UOM_QTY = "@UOM_QTY";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VENDOR_ID = "@VENDOR_ID";
        private string WH_ID = "@WH_ID";

        public bool CheckInvTransIDUnique(string invTransID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANS_ID", invTransID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_INV_TRANS_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckInvTransIDUnique(DataBase dataBase, DbTransaction tran, string invTransID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANS_ID", invTransID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_INV_TRANS_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public DataSet GetAllInvTrans(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_INV_TRANS.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public List<InvTransInfo> GetInvTransByExecuteDate(string ExecuteDate, string whLoginID)
        {
            string sql = this.SQL_SELECT_INV_TRANS.Replace("@_@", whLoginID).Replace("@CREATED_DATE", ExecuteDate);
            List<InvTransInfo> invTransInfoList = new List<InvTransInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    DateTime CS00001;
                    InvTransInfo invTransInfo = new InvTransInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TRANS_ID"].ToString(), reader["TRANS_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["DOC_ID"].ToString(), (reader["DOC_LINE_ID"].ToString() != "") ? int.Parse(reader["DOC_LINE_ID"].ToString()) : 0, reader["SOURCE_TRANS_TYPE"].ToString(), reader["STATUS"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00001 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00001 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00001 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? ((double) float.Parse(reader["CUBE"].ToString())) : ((double) 0f), (reader["GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["GROSS_WGT"].ToString())) : ((double) 0f), (reader["NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["NET_WGT"].ToString())) : ((double) 0f), (reader["OTHER_UNIT1"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT1"].ToString())) : ((double) 0f), (reader["OTHER_UNIT2"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT2"].ToString())) : ((double) 0f), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_CALC"].ToString() != "") ? int.Parse(reader["UOM_CALC"].ToString()) : 0, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["HOLD_CODE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00001 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00001 = new DateTime()), "", reader["VENDOR_ID"].ToString(), "", "", "", (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["TRANS_DATE"].ToString() != "") ? DateTime.Parse(reader["TRANS_DATE"].ToString()) : (CS00001 = new DateTime()));
                    invTransInfoList.Add(invTransInfo);
                }
            }
            return invTransInfoList;
        }

        public InvTransInfo GetInvTransByID(string invtransID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_INV_TRANS.Replace("@_@", whLoginID) + " WHERE IT.TRANS_ID = @TRANS_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANS_ID", invtransID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            InvTransInfo invTransInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    invTransInfo = new InvTransInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TRANS_ID"].ToString(), reader["TRANS_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["DOC_ID"].ToString(), (reader["DOC_LINE_ID"].ToString() != "") ? int.Parse(reader["DOC_LINE_ID"].ToString()) : 0, reader["SOURCE_TRANS_TYPE"].ToString(), reader["STATUS"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? ((double) float.Parse(reader["CUBE"].ToString())) : ((double) 0f), (reader["GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["GROSS_WGT"].ToString())) : ((double) 0f), (reader["NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["NET_WGT"].ToString())) : ((double) 0f), (reader["OTHER_UNIT1"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT1"].ToString())) : ((double) 0f), (reader["OTHER_UNIT2"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT2"].ToString())) : ((double) 0f), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_CALC"].ToString() != "") ? int.Parse(reader["UOM_CALC"].ToString()) : 0, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["HOLD_CODE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["DESCR"].ToString(), reader["VENDOR_ID"].ToString(), reader["TRANS_TYPE_D"].ToString(), reader["SOURCE_TRANS_TYPE_D"].ToString(), reader["STATUS_D"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["TRANS_DATE"].ToString() != "") ? DateTime.Parse(reader["TRANS_DATE"].ToString()) : new DateTime());
                }
            }
            return invTransInfo;
        }

        public InvTransInfo GetInvTransByID(DataBase dataBase, DbTransaction tran, string invtransID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_INV_TRANS.Replace("@_@", whLoginID) + " WHERE IT.TRANS_ID = @TRANS_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANS_ID", invtransID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            InvTransInfo invTransInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                invTransInfo = new InvTransInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TRANS_ID"].ToString(), reader["TRANS_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["DOC_ID"].ToString(), (reader["DOC_LINE_ID"].ToString() != "") ? int.Parse(reader["DOC_LINE_ID"].ToString()) : 0, reader["SOURCE_TRANS_TYPE"].ToString(), reader["STATUS"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? ((double) float.Parse(reader["CUBE"].ToString())) : ((double) 0f), (reader["GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["GROSS_WGT"].ToString())) : ((double) 0f), (reader["NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["NET_WGT"].ToString())) : ((double) 0f), (reader["OTHER_UNIT1"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT1"].ToString())) : ((double) 0f), (reader["OTHER_UNIT2"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT2"].ToString())) : ((double) 0f), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_CALC"].ToString() != "") ? int.Parse(reader["UOM_CALC"].ToString()) : 0, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["HOLD_CODE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["DESCR"].ToString(), reader["VENDOR_ID"].ToString(), reader["TRANS_TYPE_D"].ToString(), reader["SOURCE_TRANS_TYPE_D"].ToString(), reader["STATUS_D"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["TRANS_DATE"].ToString() != "") ? DateTime.Parse(reader["TRANS_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return invTransInfo;
        }

        public DataSet GetInvTransByQueryList(List<string> sqlWhere, InvTransQueryEntity invtransQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_INV_TRANS.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!invtransQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, invtransQuery.CurrentPage, invtransQuery.PageSize, invtransQuery.SortField, invtransQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public InvTransInfo GetInvTransByReceiptID(DataBase dataBase, DbTransaction tran, string receiptID, string receiptLineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_INV_TRANS.Replace("@_@", whLoginID) + " WHERE IT.RECEIPT_ID = @RECEIPT_ID AND IT.RECEIPT_LINE_ID = @RECEIPT_LINE_ID AND IT.TRANS_TYPE='IN' AND IT.SOURCE_TRANS_TYPE='RECEIPTDETAILADD' AND IT.STATUS='OK'   ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", receiptLineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            InvTransInfo invTransInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                invTransInfo = new InvTransInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TRANS_ID"].ToString(), reader["TRANS_TYPE"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["DOC_ID"].ToString(), (reader["DOC_LINE_ID"].ToString() != "") ? int.Parse(reader["DOC_LINE_ID"].ToString()) : 0, reader["SOURCE_TRANS_TYPE"].ToString(), reader["STATUS"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? ((double) float.Parse(reader["CUBE"].ToString())) : ((double) 0f), (reader["GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["GROSS_WGT"].ToString())) : ((double) 0f), (reader["NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["NET_WGT"].ToString())) : ((double) 0f), (reader["OTHER_UNIT1"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT1"].ToString())) : ((double) 0f), (reader["OTHER_UNIT2"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT2"].ToString())) : ((double) 0f), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["UOM_CALC"].ToString() != "") ? int.Parse(reader["UOM_CALC"].ToString()) : 0, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["HOLD_CODE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["DESCR"].ToString(), reader["VENDOR_ID"].ToString(), reader["TRANS_TYPE_D"].ToString(), reader["SOURCE_TRANS_TYPE_D"].ToString(), reader["STATUS_D"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["TRANS_DATE"].ToString() != "") ? DateTime.Parse(reader["TRANS_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return invTransInfo;
        }

        public int InsertInvTrans(InvTransInfo invTransInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invTransInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvTrans_Parameters(invTransInfo);
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_INSERT_INV_TRANS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertInvTrans(DataBase dataBase, DbTransaction tran, InvTransInfo invTransInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invTransInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvTrans_Parameters(invTransInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_INV_TRANS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_InvTrans_Parameters(InvTransInfo invTransInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.TRANS_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TRANS_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.LOT, SqlDbType.VarChar, 20), new SqlParameter(this.FROM_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.FROM_LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DOC_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DOC_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.SOURCE_TRANS_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR01, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR02, SqlDbType.VarChar, 50), 
                new SqlParameter(this.LOT_ATTR03, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR04, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR05, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR06, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR07, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR08, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR09, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR10, SqlDbType.VarChar, 50), new SqlParameter(this.PRODUCE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPIRY_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.RECEIPT_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXTERNAL_LOT, SqlDbType.VarChar, 100), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.CASE_CNT, SqlDbType.Decimal, 13), new SqlParameter(this.INNER_PACK, SqlDbType.Decimal, 13), new SqlParameter(this.PALLET, SqlDbType.Decimal, 13), 
                new SqlParameter(this.CUBE, SqlDbType.Float, 8), new SqlParameter(this.GROSS_WGT, SqlDbType.Float, 8), new SqlParameter(this.NET_WGT, SqlDbType.Float, 8), new SqlParameter(this.OTHER_UNIT1, SqlDbType.Float, 8), new SqlParameter(this.OTHER_UNIT2, SqlDbType.Float, 8), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.UOM_CALC, SqlDbType.Int, 4), new SqlParameter(this.UOM_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.RECEIPT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.RECEIPT_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.HOLD_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 20), new SqlParameter(this.UNIT_PRICE, SqlDbType.Float), 
                new SqlParameter(this.TRANS_DATE, SqlDbType.DateTime, 8)
             };
            if (!string.IsNullOrEmpty(invTransInfo.WhID))
            {
                paramArray[0].Value = invTransInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = invTransInfo.TransID;
            paramArray[2].Value = invTransInfo.TransType;
            paramArray[3].Value = invTransInfo.OwnerID;
            paramArray[4].Value = invTransInfo.SkuID;
            paramArray[5].Value = invTransInfo.Lot;
            paramArray[6].Value = invTransInfo.FromLoc;
            paramArray[7].Value = invTransInfo.FromLpnID;
            paramArray[8].Value = invTransInfo.ToLoc;
            paramArray[9].Value = invTransInfo.ToLpnID;
            if (!string.IsNullOrEmpty(invTransInfo.DocID))
            {
                paramArray[10].Value = invTransInfo.DocID;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            paramArray[11].Value = invTransInfo.DocLineID;
            if (!string.IsNullOrEmpty(invTransInfo.SourceTransType))
            {
                paramArray[12].Value = invTransInfo.SourceTransType;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(invTransInfo.Status))
            {
                paramArray[13].Value = invTransInfo.Status;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            paramArray[14].Value = invTransInfo.LotAttr01;
            paramArray[15].Value = invTransInfo.LotAttr02;
            paramArray[0x10].Value = invTransInfo.LotAttr03;
            if (!string.IsNullOrEmpty(invTransInfo.LotAttr04))
            {
                paramArray[0x11].Value = invTransInfo.LotAttr04;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(invTransInfo.LotAttr05))
            {
                paramArray[0x12].Value = invTransInfo.LotAttr05;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            paramArray[0x13].Value = invTransInfo.LotAttr06;
            paramArray[20].Value = invTransInfo.LotAttr07;
            paramArray[0x15].Value = invTransInfo.LotAttr08;
            paramArray[0x16].Value = invTransInfo.LotAttr09;
            paramArray[0x17].Value = invTransInfo.LotAttr10;
            DateTime CS00003 = new DateTime();
            if (invTransInfo.ProduceDate != CS00003)
            {
                paramArray[0x18].Value = invTransInfo.ProduceDate;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (invTransInfo.ExpiryDate != CS00003)
            {
                paramArray[0x19].Value = invTransInfo.ExpiryDate;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (invTransInfo.ReceiptDate != CS00003)
            {
                paramArray[0x1a].Value = invTransInfo.ReceiptDate;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(invTransInfo.ExternalLot))
            {
                paramArray[0x1b].Value = invTransInfo.ExternalLot;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            paramArray[0x1c].Value = invTransInfo.Qty;
            paramArray[0x1d].Value = invTransInfo.CaseCnt;
            paramArray[30].Value = invTransInfo.InnerPack;
            paramArray[0x1f].Value = invTransInfo.Pallet;
            paramArray[0x20].Value = invTransInfo.Cube;
            paramArray[0x21].Value = invTransInfo.GrossWgt;
            paramArray[0x22].Value = invTransInfo.NetWgt;
            paramArray[0x23].Value = invTransInfo.OtherUnit1;
            paramArray[0x24].Value = invTransInfo.OtherUnit2;
            if (!string.IsNullOrEmpty(invTransInfo.PackID))
            {
                paramArray[0x25].Value = invTransInfo.PackID;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(invTransInfo.UomID))
            {
                paramArray[0x26].Value = invTransInfo.UomID;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            paramArray[0x27].Value = invTransInfo.UomCalc;
            paramArray[40].Value = invTransInfo.UomQty;
            if (!string.IsNullOrEmpty(invTransInfo.ReceiptID))
            {
                paramArray[0x29].Value = invTransInfo.ReceiptID;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            paramArray[0x2a].Value = invTransInfo.ReceiptLineID;
            if (!string.IsNullOrEmpty(invTransInfo.HoldCode))
            {
                paramArray[0x2b].Value = invTransInfo.HoldCode;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            paramArray[0x2c].Value = invTransInfo.CreatedBy;
            paramArray[0x2d].Value = invTransInfo.UpdatedBy;
            if (!string.IsNullOrEmpty(invTransInfo.VendorID))
            {
                paramArray[0x2e].Value = invTransInfo.VendorID;
            }
            else
            {
                paramArray[0x2e].Value = DBNull.Value;
            }
            paramArray[0x2f].Value = invTransInfo.UnitPrice;
            if (invTransInfo.TransDate != new DateTime())
            {
                paramArray[0x30].Value = invTransInfo.TransDate;
                return paramArray;
            }
            paramArray[0x30].Value = DateTime.Now;
            return paramArray;
        }

        public int UpdateInvTrans(InvTransInfo invTransInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invTransInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvTrans_Parameters(invTransInfo);
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_UPDATE_INV_TRANS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateInvTrans(DataBase dataBase, DbTransaction tran, InvTransInfo invTransInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invTransInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvTrans_Parameters(invTransInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_INV_TRANS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateInvTransUnitPriceByLot(DataBase dataBase, DbTransaction tran, string lot, float unitPrice, string whLoginID, string updateBy)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@UNIT_PRICE", unitPrice), new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@LOT", lot) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_INV_TRANS_UNIT_PRICE_BY_LOT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateTransCreatedDateByDocID(DataBase dataBase, DbTransaction tran, string DocID, string transType, string sourceTransType, string UpdateBy, string ActualShipDate, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANS_DATE", ActualShipDate), new SqlParameter("@UPDATED_DATE", ActualShipDate), new SqlParameter("@DOC_ID", DocID), new SqlParameter("@TRANS_TYPE", transType), new SqlParameter("@SOURCE_TRANS_TYPE", sourceTransType), new SqlParameter("@UPDATED_BY", UpdateBy) };
                if (paras != null)
                {
                    result = dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CREATED_DATE_BY_DOC_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateTransStatus(DataBase dataBase, DbTransaction tran, string TransID, string UpdateBy, string transStatus, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", transStatus), new SqlParameter("@TRANS_ID", TransID), new SqlParameter("@UPDATED_BY", UpdateBy) };
                if (paras != null)
                {
                    result = dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_STATUS_FOR_RECEIPT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateTransStatusByDocID(DataBase dataBase, DbTransaction tran, string DocID, string transType, string sourceTransType, string UpdateBy, string transStatus, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", transStatus), new SqlParameter("@DOC_ID", DocID), new SqlParameter("@TRANS_TYPE", transType), new SqlParameter("@SOURCE_TRANS_TYPE", sourceTransType), new SqlParameter("@UPDATED_BY", UpdateBy) };
                if (paras != null)
                {
                    result = dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_STATUS_BY_DOC_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

